package kr.co.choongang;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet("/EmpSelectPoolAnno")
public class EmpSelectPoolAnnoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=EUC-KR");
		PrintWriter out = response.getWriter();
		out.print("<html><body>");
		
		ArrayList<EmpDTO> list=select();
		for(EmpDTO dto : list) {
			String emp_id=dto.getEmp_id();
			String ename=dto.getEname();
			int salary=dto.getSalary();
			String depart=dto.getDepart();
			
			out.print(emp_id+"\t"+ename+"\t"+salary+"\t"+depart+"<br>");
		}
		out.print("</html></body>");
		
	}
	@Resource(name="jdbc/Oracle11g") javax.sql.DataSource dataFactory;
	
	public ArrayList<EmpDTO> select() {
		ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		
		try {
			con=((Statement) dataFactory).getConnection();
			String sql="select emp_id, ename, salary, depart from emp2";
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				String emp_id=rs.getString("emp_id");
				String ename=rs.getString("ename");
				int salary=rs.getInt("salary");
				String depart=rs.getString("depart");
			
				EmpDTO dto=new EmpDTO(emp_id, ename, salary, depart);
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
				try {
					if(rs!=null)rs.close();
					if(pstmt!=null)pstmt.close();
					if(con!=null)con.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
		
		}//end select
}//end class
	
	
	


